﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Dapper
{
    public class Database
    {
        /// 得到web.config里配置项的数据库连接字符串。  
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        /// 得到工厂提供器类型  
        //private static readonly string ProviderFactoryString = ConfigurationManager.AppSettings["DBProvider"].ToString();
        private static readonly string ProviderFactoryString = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
        private static DbProviderFactory df = null;
        /// <summary>  
        /// 创建工厂提供器
        /// </summary>  
        public static IDbConnection GetConnection()
        {
            if (df == null)
                df = DbProviderFactories.GetFactory(ProviderFactoryString);
            var connection = df.CreateConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            return connection;
        }
        /// <summary>
        /// 初始化方法。。。
        /// </summary>
        public static void Init()
        {
            switch (ProviderFactoryString)
            {
                case "System.Data.OracleClient": InitOracle(); break;
                case "System.Data.SqlClient": InitSql(); break;
            }
        }

        /// <summary>
        /// 初始化方法，其中包括创建SqlServer数据库，根据Model创建表
        /// </summary>
        public static void InitSql()
        {
            #region 创建数据库
            //初始链接字符串
            string connectionStr = ConfigurationManager.ConnectionStrings["InitConStr"].ConnectionString;
            //程序路径
            //string rootPath = Directory.GetCurrentDirectory();
            //rootPath = rootPath.Substring(0, rootPath.IndexOf("MyOnwerProject"));
            ////数据库存储路径
            //string dbPath = rootPath + @"MyOnwerProject\Project.Web\App_Data\";

            string dbPath = ConfigurationManager.AppSettings["DbPath"];
            string dbName = ConnectionString.Substring(ConnectionString.IndexOf("catalog=") + 8,
                                                       ConnectionString.IndexOf(";user") -
                                                       ConnectionString.IndexOf("catalog=") - 8);

            //判断是否有数据库，如果没有就创建一个和配置文件中一致的数据库
            using (var connection = new SqlConnection(connectionStr))
            {
                connection.Open();
                string sql = "select count(1) from sys.databases where name=@dbName";
                var isDbNull = connection.Query<int>(sql, new { dbName = dbName }).First();
                if (isDbNull == 0)
                {
                    string createDb = string.Format(@"create database {0}
                                        on primary
                                        (
                                        name='DapperProject',
                                        filename='{1}DapperProject.mdf',
                                        size=10MB,
                                        maxsize=100MB,
                                        filegrowth=10%

                                        )
                                        log on
                                        (
                                        name='DapperProject_log',
                                        filename='{1}DapperProject_log.mdf',
                                        size=5MB,
                                        filegrowth=10%
                                        )", dbName, dbPath);
                    connection.Execute(createDb);
                }
            }


            #endregion
            //创建表
            CreateTable();
        }


        /// <summary>
        /// 初始化方法，其中包括创建SqlServer数据库，根据Model创建表
        /// </summary>
        public static void InitOracle()
        {
            #region 创建数据库
            //初始链接字符串
            string connectionStr = ConfigurationManager.ConnectionStrings["InitConStr"].ConnectionString;

            //数据库存储路径
            string dbPath = ConfigurationManager.AppSettings["DbPath"];
            //获取表空间名字
            string tableSpace = ConfigurationManager.AppSettings["tableSpace"];
            //Password=testenv;Persist Security Info=True;User ID=testenv;Data Source=ORCL;

            string userId = ConnectionString.Substring(ConnectionString.IndexOf("User ID=") + 8,
                                                       ConnectionString.IndexOf(";Data") -
                                                       ConnectionString.IndexOf("User ID=") - 8);

            string password = ConnectionString.Substring(ConnectionString.IndexOf("Password=") + 9,
                                                       ConnectionString.IndexOf(";Persist") -
                                                       ConnectionString.IndexOf("Password=") - 9);
            //查看表空间
            string spaceSql = String.Format(@"select count(*) from dba_tablespaces where tablespace_name='{0}'", tableSpace.ToUpper());
            //查看临时表空间
            string tempSpaceSql = String.Format(@"select count(*) from dba_tablespaces where tablespace_name='{0}'", tableSpace.ToUpper());
            //查看用户
            string userSql = string.Format("select count(1) from ALL_USERS  where username='{0}'", userId.ToUpper());
            //查看用户角色
            string userRoleSql = string.Format("select * from dba_role_privs where grantee ='{0}'", userId.ToUpper());
            //查看用户权限
            string userActionSql = string.Format("SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='{0}'", userId.ToUpper());


            //判断是否有数据库，如果没有就创建一个和配置文件中一致的数据库
            using (var connection = new OracleConnection(connectionStr))
            {
                #region 暂时不用。。。
                //                connection.Open();
                //                var trun = connection.BeginTransaction();
                //                string sql = string.Format(
                //                            @"declare v_cnt number;
                //                            begin
                //                            select count(0) into v_cnt from dba_tablespaces where tablespace_name='{0}';
                //                            if v_cnt = 0 then
                //                            execute immediate ' create tablespace {1} logging datafile ''{2}{1}.dbf'' size 200m autoextend on next 50m extent management local ';     
                //                            end if;
                //                            end;", tableSpace.ToUpper(), tableSpace, dbPath);


                //                connection.Execute(sql,null,trun);
                //                trun.Commit(); 
                #endregion

                var count = 0;
                connection.Open();

                var spaceResult = connection.Query<decimal>(spaceSql).First();
                var tempSpaceResult = connection.Query<decimal>(tempSpaceSql).First();
                var userResult = connection.Query<decimal>(userSql).First();
                var userRoleResult = connection.Query(userRoleSql);
                var userActionResult = connection.Query(userActionSql);
                //判断是否有该表空间，如果没有 就创建一个
                if (spaceResult == 0)
                {
                    string createSpaceSql = string.Format(@"create tablespace {0} logging datafile '{1}{0}.dbf' size 200m autoextend on next 50m extent management local ", tableSpace, dbPath);
                    connection.Execute(createSpaceSql);
                }
                if (tempSpaceResult == 0)
                {
                    string createTempSpaceSql = string.Format(@"create temporary tablespace {0}temp tempfile '{1}{0}temp.dbf' size 100m autoextend on next 50m extent management local ", tableSpace, dbPath);
                    connection.Execute(createTempSpaceSql);
                }
                if (userResult == 0)
                {
                    //创建用户，并赋给其所有权限
                    string createUser = string.Format(@"CREATE USER {0} IDENTIFIED BY {1} 
                                                    DEFAULT TABLESPACE {2}
                                                    TEMPORARY TABLESPACE {2}temp", userId.ToLower(), password.ToLower(), tableSpace);
                    connection.Execute(createUser);
                }
                //用户角色
                if (!userRoleResult.Any(r => r.GRANTED_ROLE == "CONNECT"))
                {
                    connection.Execute(string.Format("GRANT CONNECT TO {0}", userId.ToUpper()));
                }
                if (!userRoleResult.Any(r => r.GRANTED_ROLE == "RESOURCE"))
                {
                    connection.Execute(string.Format("GRANT RESOURCE TO {0}", userId.ToUpper()));
                }
                if (!userRoleResult.Any(r => r.GRANTED_ROLE == "DBA"))
                {
                    connection.Execute(string.Format("GRANT DBA TO {0}", userId.ToUpper()));
                }
                //用户权限
                if (!userActionResult.Any(a => a.PRIVILEGE == "CREATE TABLE"))
                {
                    connection.Execute(string.Format("GRANT CREATE TABLE TO {0}", userId.ToUpper()));
                }
                if (!userActionResult.Any(a => a.PRIVILEGE == "CREATE VIEW"))
                {
                    connection.Execute(string.Format("GRANT CREATE VIEW TO {0}", userId.ToUpper()));
                }
                if (!userActionResult.Any(a => a.PRIVILEGE == "CREATE DATABASE LINK"))
                {
                    connection.Execute(string.Format("GRANT CREATE DATABASE LINK TO {0}", userId.ToUpper()));
                }

            }

            #endregion

            CreateTable();
        }
        /// <summary>
        /// 创建表
        /// </summary>
        public static void CreateTable()
        {
            #region 创建表
            //反射出Model中的所有的类，生成sql语句
            //保存所有的表名
            Dictionary<string, Type> tableDict = new Dictionary<string, Type>();
            Assembly assembly = Assembly.Load("Project.Model");

            foreach (var type in assembly.GetTypes())
            {
                tableDict.Add(type.Name, type);
            }

            //遍历所有的类
            foreach (Type t in assembly.GetTypes())
            {
                StringBuilder sb = new StringBuilder();
                if (ProviderFactoryString == "System.Data.SqlClient")
                {
                    sb.Append(string.Format("if exists (select * from sysobjects where [name] = '{0}' and xtype='U')",
                                            t.Name));
                    sb.Append(string.Format("drop table {0} \n ", t.Name));
                }
                else if (ProviderFactoryString == "System.Data.OracleClient")
                {
                    //判断表是否存在  如果存在就删除
                    string sql = string.Format(@"declare v_cnt number;  begin  select count(*) into v_cnt from user_tables t where t.TABLE_NAME='{0}';  if v_cnt > 0 then  execute immediate ' drop table {0} ';  end if;  end;", t.Name.ToUpper());

                    using (var con = GetConnection())
                    {
                        con.Execute(sql);
                    }
                }
                sb.Append(string.Format("create table {0} ", t.Name));
                sb.Append("( ");
                //遍历类中的属性
                foreach (var p in t.GetProperties())
                {
                    //表有关联,忽略关联
                    if (!tableDict.Keys.Contains(p.PropertyType.Name))
                    {
                        //获取属性标签
                        var attribute = p.GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                        //有标签的情况下将标签信息传到GetType方法内部
                        //没有标签的话就是用GetType的默认属性
                        if (attribute != null)
                        {
                            sb.Append(string.Format("{0} {1},", p.Name,
                                                    GetDbType(p.PropertyType.Name, attribute.IsNullable,
                                                              attribute.PrimaryKey, attribute.IsIdentity,
                                                              attribute.Length)));
                        }
                        //这里是之前对关联表的操作 现在用不到了，先留着 或许会用到
                        //else
                        //{
                        //    var currentTbale = tableDict[p.PropertyType.Name];
                        //    sb.Append(string.Format("{0} {1},", p.Name + "Id", GetDbType("int32", attribute.IsNullable, attribute.PrimaryKey, attribute.IsIdentity, attribute.Length)));
                        //}

                        else
                        {
                            sb.Append(string.Format("{0} {1},", p.Name, GetDbType(p.PropertyType.Name)));
                        }
                    }
                }
                var createTableSql = sb.ToString();
                createTableSql = createTableSql.Substring(0, createTableSql.Length - 1) + " )";
                //创建表
                using (var connection = GetConnection())
                {
                    connection.Execute(createTableSql);
                }
            }
            #endregion

        }


        /// <summary>
        /// 根据属性标签获取相对应的数据库类型
        /// </summary>
        /// <param name="type">数据类型exp:Int32,String</param>
        /// <param name="isNullable">是否为空，默认为空</param>
        /// <param name="isPrimaryKey">是否为主键，默认不为主键</param>
        /// <param name="isIdentity">是否自增，默认不为自增</param>
        /// <param name="length">长度</param>
        /// <returns>对应的数据库类型</returns>
        public static string GetDbType(string type, bool isNullable = true, bool isPrimaryKey = false, bool isIdentity = false, int? length = null)
        {
            StringBuilder sb = new StringBuilder();
            //sb.Append(" ");
            //这里判断数据库
            if (ProviderFactoryString == "System.Data.SqlClient")
            {
                //判断数据类型
                switch (type.ToLower())
                {
                    case "int16": sb.Append("smallint"); break;
                    case "int32": sb.Append("int"); break;
                    case "int64": sb.Append("bigint"); break;
                    case "string":
                        if (length != null && length != -1)
                        {
                            sb.Append(string.Format("nvarchar({0})", length));
                        }
                        else
                        {
                            sb.Append("nvarchar(max)");
                        }
                        break;
                    case "boolean": sb.Append("bit"); break;
                    case "datetime": sb.Append("datetime"); break;
                    case "decimal": sb.Append("decimal"); break;
                    case "double": sb.Append("float"); break;
                    case "guid": sb.Append("uniqueidentifier"); break;
                    case "object": sb.Append("variant"); break;
                    case "enum": sb.Append("bit"); break;
                    //枚举
                    default: sb.Append("bit"); break;
                }
                //自增
                if (isIdentity)
                {
                    sb.Append(" identity(1,1)");
                }
            }
            else if (ProviderFactoryString == "System.Data.OracleClient")
            {
                switch (type.ToLower())
                {
                    case "int16": sb.Append("number(4)"); break;
                    case "int32": sb.Append("number(5)"); break;
                    case "int64": sb.Append("number(8)"); break;
                    case "decimal": sb.Append("number(29,5)"); break;
                    case "boolean": sb.Append("number(1)"); break;
                    case "double": sb.Append("number(15,5)"); break;
                    case "string":
                        if (length != null && length != -1)
                        {
                            sb.Append(string.Format("varchar2({0})", length));
                        }
                        else
                        {
                            sb.Append("varchar2(512)");
                        }
                        break;
                    case "datetime": sb.Append("date"); break;
                    //枚举
                    default: sb.Append("number(1)"); break;
                }
            }


            //主键
            if (isPrimaryKey)
            {
                sb.Append(" primary key");
            }
            //为空
            if (isNullable)
            {
                sb.Append(" null");
            }
            else
            {
                sb.Append(" not null");
            }

            return sb.ToString();
        }
    }
}